package com.xhx.accounting;

import android.content.ContentValues;
import android.content.Context;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.ListView;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.Collections;
import java.util.Comparator;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBDao {
    private final DBHelper dbHelper;
    private final Context mcontext;
    private String Record;
    private String SavePlanList;
    private DecimalFormat decimalFormat;

    public DBDao(Context context) {
        dbHelper = new DBHelper(context);
        dbHelper.getWritableDatabase();
        mcontext = context;
        decimalFormat = new DecimalFormat("0.00");
    }

    public List<Map<String, Object>> getClassifi(int K) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Cursor classifiList;
        classifiList = db.rawQuery("select * from Classification where classification_direction =" + K, null);
        if (classifiList.moveToFirst()) {
            do {
                Map<String, Object> map = new HashMap<>();
                String name = classifiList.getString(classifiList.getColumnIndex("classification_name"));
                String icon = classifiList.getString(classifiList.getColumnIndex("classification_icon"));
                int id = classifiList.getInt(classifiList.getColumnIndex("id"));
                int direction = classifiList.getInt(classifiList.getColumnIndex("classification_direction"));
                map.put("id", id);
                map.put("classification_name", name);
                map.put("classification_icon", icon);
                map.put("classification_direction", direction);
                Log.d("Result", map.toString());
                result.add(map);
            } while (classifiList.moveToNext());
        }
        classifiList.close();
        db.close();
        return result;
    }

    public List<Map<String, Object>> getAccountBook() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Cursor accountList;
        accountList = db.rawQuery("select * from AccountBook", null);
        if (accountList.moveToFirst()) {
            do {
                Map<String, Object> map = new HashMap<>();
                String name = accountList.getString(accountList.getColumnIndex("accountbook_name"));
                int id = accountList.getInt(accountList.getColumnIndex("id"));
                map.put("id", id);
                map.put("accountbook_name", name);
                result.add(map);
            } while (accountList.moveToNext());
        }
        accountList.close();
        db.close();
        return result;
    }

    public int getCurrentAccountBook() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Cursor currentAccountBook;
        currentAccountBook = db.rawQuery("select * from AccountBook where isCurrentAccountBook = 1 ", null);
        currentAccountBook.moveToFirst();
        return currentAccountBook.getInt(currentAccountBook.getColumnIndex("id"));
    }

    public void changeCurrentAccountBook(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String recoverId = "update AccountBook set isCurrentAccountBook = 0 where isCurrentAccountBook = 1";
        String updateId = "update AccountBook set isCurrentAccountBook = 1 where id=" + id;
        db.execSQL(recoverId);
        db.execSQL(updateId);
    }

    public long addAccountBook(String accountBookName) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("accountbook_name", accountBookName);
        values.put("isCurrentAccountBook", 0);
        long rowid = db.insert("AccountBook", null, values);
        values.clear();
        return rowid;
    }

    public void addSavingRecord(long date, int planId, int money) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("date", date);
        values.put("planId", planId);
        values.put("money", money);
        db.insert("Saving", null, values);
        db.close();
    }

    public void deleteAccountBook(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        changeCurrentAccountBook(1);
        String deleteAccountBook = "delete from AccountBook where id =" + id;
        String moveRecord = "update Record set accountBookId = 1 where accountBookId = " + id;
        db.execSQL(moveRecord);
        db.execSQL(deleteAccountBook);
    }

    public List<Map<String, Object>> searchRecord(String name, int accountId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Cursor classificationResult;
        classificationResult = db.rawQuery("select * from Classification where classification_name like '%" + name + "%'", null);
        if (classificationResult.moveToFirst()) {
            int classificationId = classificationResult.getInt(classificationResult.getColumnIndex("id"));
            Log.d("classificationId", String.valueOf(classificationId));
            //拿到分类icon和name
            String typeName = new String();
            String typeIcon = new String();
            int classification_direction = 0;
            Cursor type;
            type = db.rawQuery("select * from Classification where id=" + classificationId, null);
            if (type.moveToFirst()) {
                typeName = type.getString(type.getColumnIndex("classification_name"));
                typeIcon = type.getString(type.getColumnIndex("classification_icon"));
                classification_direction = type.getInt(type.getColumnIndex("classification_direction"));
            }
            //按照天拿该分类的记录
            Cursor Record;
            Record = db.rawQuery("select timeStamp,count(*) from Record where accountBookId = " + accountId + " and classification = " + classificationId + " group by timeStamp order by timeStamp desc", null);
            if (Record.moveToFirst()) {
                do {
                    Cursor timeRecord;
                    List<Map<String, Object>> recordCurrent = new ArrayList<>();
                    long timeStamp = Record.getLong(Record.getColumnIndex("timeStamp"));
                    Map<String, Object> map2 = new HashMap<>();
                    map2.put("timeStamp", timeStamp);
                    double cost = 0.0;
                    double income = 0.0;
                    timeRecord = db.rawQuery("select * from Record where accountBookId=" + accountId + " and classification =" + classificationId + " AND timeStamp=" + timeStamp, null);
                    if (timeRecord.moveToFirst()) {
                        do {
                            Map<String, Object> map = new HashMap<>();
                            double amount = timeRecord.getDouble(timeRecord.getColumnIndex("amount"));
                            int typeId = timeRecord.getInt(timeRecord.getColumnIndex("classification"));
                            int typeNum = timeRecord.getInt(timeRecord.getColumnIndex("type"));
                            if (typeNum == -1) {
                                cost = cost + amount;
                            } else if (typeNum == 1) {
                                income = income + amount;
                            }
                            map.put("amount", decimalFormat.format(amount));
                            map.put("typeName", typeName);
                            map.put("typeIcon", typeIcon);
                            map.put("classification_direction", classification_direction);
                            recordCurrent.add(map);
                        } while (timeRecord.moveToNext());
                    }
                    map2.put("recordAll", recordCurrent);
                    map2.put("cost", decimalFormat.format(cost));
                    map2.put("income", decimalFormat.format(income));
                    result.add(map2);
                } while (Record.moveToNext());
            }
        }
        return result;
    }


    public List<Map<String, Object>> getChartData(Long startTime, Long endTime, int type) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        //查询到所有的分类
        Cursor classifiList;
        classifiList = db.rawQuery("select * from Classification where classification_direction =" + type, null);
        if (classifiList.moveToFirst()) {
            do {
                Map<String, Object> map = new HashMap<>();
                String name = classifiList.getString(classifiList.getColumnIndex("classification_name"));
                int id = classifiList.getInt(classifiList.getColumnIndex("id"));
                float total = 0;
                map.put("id", id);
                map.put("classification_name", name);
                Cursor eachClassifi = db.rawQuery("select * from Record where classification = " + id + " AND timeStamp between " + startTime + " AND " + endTime, null);
                if (eachClassifi.moveToFirst()) {
                    do {
                        total = total + eachClassifi.getFloat(eachClassifi.getColumnIndex("amount"));
                    } while (eachClassifi.moveToNext());
                    map.put("amount", total);
                    result.add(map);
                }
            } while (classifiList.moveToNext());
        }

        //从大到小排序
        Collections.sort(result, new Comparator<Map<String, Object>>() {
            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Float amount1 = new Float((float) o1.get("amount"));
                Float amount2 = new Float((float) o2.get("amount"));
                return -amount1.compareTo(amount2);
            }
        });

        //返回
        Log.d("Result", result.toString());
        return result;
    }

    public List<Map<String, Object>> getLineChartData(Long startTime, Long endTime, int type) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Long onDay = Long.valueOf(24 * 60 * 60);
        Long time = startTime;
        int n = 0;
        while (time <= endTime) {
            Map<String, Object> map = new HashMap<>();
            float total = 0;

            Cursor timeRecord;
            timeRecord = db.rawQuery("select * from Record where type = " + type + " And timeStamp=" + time, null);
            if (timeRecord.moveToFirst()) {
                do {
                    total = total + timeRecord.getFloat(timeRecord.getColumnIndex("amount"));
                } while (timeRecord.moveToNext());
            }
            timeRecord.close();
            time = time + onDay;//下一天
            n = n + 1;//计数
            map.put("dayId", n);
            map.put("total", total);
            result.add(map);

        }
        return result;
    }

    public List<Map<String, Object>> getSavePlan() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Cursor savePlanList;
        savePlanList = db.rawQuery("select * from SavePlan", null);
        if (savePlanList.moveToFirst()) {
            do {
                Map<String, Object> map = new HashMap<>();
                String name = savePlanList.getString(savePlanList.getColumnIndex("saveplan_name"));
                int id = savePlanList.getInt(savePlanList.getColumnIndex("id"));
                int goal = savePlanList.getInt(savePlanList.getColumnIndex("goal"));
                Cursor saveRecord;
                saveRecord = db.rawQuery("select SUM(money) as sum from Saving where planId =" + id, null);
                if (saveRecord.moveToFirst()) {
                    int sumMoney = saveRecord.getInt(saveRecord.getColumnIndex("sum"));
                    Log.d("sum",String.valueOf(sumMoney));
                    if(sumMoney >= goal){
                        map.put("complete",1);
                    }else{
                        map.put("complete",0);
                    }
                }
                map.put("id", id);
                map.put("saveplan_name", name);
                result.add(map);
            } while (savePlanList.moveToNext());
        }
        savePlanList.close();
        db.close();
        return result;
    }

    public Map<String, Object> getPlanDetail(int planId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Map<String, Object> map = new HashMap<>();
        int real = 0;
        Cursor planDetail = db.rawQuery("select * from SavePlan where id = " + planId, null);
        if (planDetail.moveToFirst()) {
            String saveplan_name = planDetail.getString(planDetail.getColumnIndex("saveplan_name"));
            int goal = planDetail.getInt(planDetail.getColumnIndex("goal"));
            long start_time = planDetail.getLong(planDetail.getColumnIndex("start_time"));
            long deadline = planDetail.getLong(planDetail.getColumnIndex("deadline"));
            map.put("saveplan_name", saveplan_name);
            map.put("goal", goal);
            map.put("start_time", start_time);
            map.put("deadline", deadline);
        }
        planDetail.close();
        Cursor saving = db.rawQuery("select * from Saving where planId = " + planId, null);
        List<Map<String, Object>> list = new ArrayList<>();
        if (saving.moveToFirst()) {
            do {
                Map<String, Object> map2 = new HashMap<>();
                long date = saving.getLong(saving.getColumnIndex("date"));
                int money = saving.getInt(saving.getColumnIndex("money"));
                int id = saving.getInt(saving.getColumnIndex("id"));
                real = real + money;
                map2.put("date", date);
                map2.put("money", money);
                map2.put("id",id);
                list.add(map2);
            } while (saving.moveToNext());
        }
        db.close();
        Log.d("list", list.toString());
        map.put("list", list);
        map.put("real", real);
        Log.d("PlanDetail", map.toString());
        return map;
    }

    public void deletePlanDetail(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String deletePlanDetail = "delete from Saving where id =" + id;
        db.execSQL(deletePlanDetail);
    }

    public long addPlan(String planName, String startTime, String deadline, String goal) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("saveplan_name", planName);
        values.put("start_time", startTime);
        values.put("deadline", deadline);
        values.put("goal", goal);
        long rowid = db.insert("SavePlan", null, values);
        values.clear();
        return rowid;
    }

    public void deleteSavePlan(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String deleteSavePlan = "delete from SavePlan where id =" + id;
        db.execSQL(deleteSavePlan);
    }

    public boolean addAccountRecord(ContentValues cv) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.insert("Record", null, cv);
        return true;
    }

    public Map<String, Object> getRecord2(Long startTime, Long endTime, int accountBook) {
        Log.d("dbaccountbook", String.valueOf(accountBook));
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        Map<String, Object> result2 = new HashMap<>();
        double cost_sum = 0.0;
        double income_sum = 0.0;
        Cursor Record;
        Record = db.rawQuery("select timeStamp,count(*) from Record where accountBookId=" + accountBook + " AND timeStamp between " + startTime + " AND " + endTime + " group by timeStamp order by timeStamp desc", null);
        if (Record.moveToFirst()) {
            do {
                Cursor timeRecord;
                List<Map<String, Object>> recordCurrent = new ArrayList<>();
                long timeStamp = Record.getLong(Record.getColumnIndex("timeStamp"));
                Map<String, Object> map2 = new HashMap<>();
                map2.put("timeStamp", timeStamp);
                double cost = 0.0;
                double income = 0.0;
                timeRecord = db.rawQuery("select * from Record where accountBookId=" + accountBook + " AND timeStamp=" + timeStamp, null);
                if (timeRecord.moveToFirst()) {
                    do {
                        Map<String, Object> map = new HashMap<>();
                        double amount = timeRecord.getDouble(timeRecord.getColumnIndex("amount"));
                        int typeId = timeRecord.getInt(timeRecord.getColumnIndex("classification"));
                        int typeNum = timeRecord.getInt(timeRecord.getColumnIndex("type"));
                        if (typeNum == -1) {
                            cost = cost + amount;
                            cost_sum = cost_sum + amount;
                        } else if (typeNum == 1) {
                            income = income + amount;
                            income_sum = income_sum + amount;
                        }
                        map.put("amount", decimalFormat.format(amount));
                        String typeName = new String();
                        String typeIcon = new String();
                        int classification_direction = 0;
                        Cursor type;
                        type = db.rawQuery("select * from Classification where id=" + typeId, null);
                        if (type.moveToFirst()) {
                            do {
                                typeName = type.getString(type.getColumnIndex("classification_name"));
                                typeIcon = type.getString(type.getColumnIndex("classification_icon"));
                                classification_direction = type.getInt(type.getColumnIndex("classification_direction"));
                            } while (type.moveToNext());
                        }
                        map.put("typeName", typeName);
                        map.put("typeIcon", typeIcon);
                        map.put("classification_direction", classification_direction);
                        recordCurrent.add(map);
                    } while (timeRecord.moveToNext());
                }
                map2.put("recordAll", recordCurrent);
                map2.put("cost", decimalFormat.format(cost));
                map2.put("income", decimalFormat.format(income));
                result.add(map2);
            } while (Record.moveToNext());
        }
        Log.d("result", String.valueOf(result));
        result2.put("cost_sum", decimalFormat.format(cost_sum));
        result2.put("income_sum", decimalFormat.format(income_sum));
        result2.put("result", result);
        return result2;
    }

    public List<Map<String, Object>> getDayRecord(Long TimeStamp) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        List<Map<String, Object>> result = new ArrayList<>();
        //拿到当前账本
        Cursor currentBook = db.rawQuery("select * from AccountBook where isCurrentAccountBook = 1", null);
        if (currentBook.moveToFirst()) {
            double cost = 0.0;
            double income = 0.0;
            int bookId = currentBook.getInt(currentBook.getColumnIndex("id"));
            Cursor dayRecord = db.rawQuery("select * from Record where timeStamp = " + TimeStamp + " AND accountBookId = " + bookId, null);
            if (dayRecord.moveToFirst()) {
                do {
                    Map<String, Object> map = new HashMap<>();
                    int id = dayRecord.getInt(dayRecord.getColumnIndex("id"));
                    double amount = dayRecord.getDouble(dayRecord.getColumnIndex("amount"));
                    int typeId = dayRecord.getInt(dayRecord.getColumnIndex("classification"));
                    int typeNum = dayRecord.getInt(dayRecord.getColumnIndex("type"));
                    String note = dayRecord.getString(dayRecord.getColumnIndex("notes"));
                    if (typeNum == -1) {
                        cost = cost + amount;
                    } else if (typeNum == 1) {
                        income = income + amount;
                    }
                    map.put("amount", decimalFormat.format(amount));
                    String typeName = new String();
                    String typeIcon = new String();
                    int classification_direction = 0;
                    Cursor type;
                    type = db.rawQuery("select * from Classification where id=" + typeId, null);
                    if (type.moveToFirst()) {
                        do {
                            typeName = type.getString(type.getColumnIndex("classification_name"));
                            typeIcon = type.getString(type.getColumnIndex("classification_icon"));
                            classification_direction = type.getInt(type.getColumnIndex("classification_direction"));
                        } while (type.moveToNext());
                    }
                    map.put("id", id);
                    map.put("typeName", typeName);
                    map.put("typeIcon", typeIcon);
                    map.put("classification_direction", classification_direction);
                    map.put("notes", note);
                    result.add(map);
                } while (dayRecord.moveToNext());
            }
        }
        return result;
    }

    public void deleteRecord(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String deleteRecordSQL = "delete from Record where id =" + id;
        db.execSQL(deleteRecordSQL);
        db.close();
    }

    public int getBudget(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int budget2 = 0;
        Cursor budget;
        budget = db.rawQuery("select * from Budget",null);
        if(budget.moveToFirst()){
            budget2 = budget.getInt(budget.getColumnIndex("money"));
        }
        return budget2;
    }

    public void changeBudget(int money){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String newBudget = "update Budget set money =" + money;
        db.execSQL(newBudget);
        db.close();
    }

    public double getRecord3(Long startTime, Long endTime) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        double sum = 0;
        Cursor c;
        c = db.rawQuery("select SUM(amount) as sum from Record where type = -1 AND timeStamp between " + startTime + " AND " + endTime, null);
        if (c.moveToFirst()) {
            sum = c.getDouble(c.getColumnIndex("sum"));
        }
        return sum;
    }
}

